package com.asap.demo.ck.orm;

import com.asap.demo.ck.util.Constants;

import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.lang3.StringUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ru.yandex.clickhouse.ClickHouseArray;
import ru.yandex.clickhouse.domain.ClickHouseDataType;

import java.io.Serializable;
import java.sql.*;
import java.util.List;
import java.util.Map;


public class DbFetcher implements Serializable{

	private static final long serialVersionUID = 7791139542387021462L;
	private static final Logger logger = LoggerFactory.getLogger(DbFetcher.class);

	Map<String,String> config = null;
	
	public DbFetcher(Map<String,String> config) {
		this.config = config;
	}


//
//	public List<String> fetchCkServer(){
//		JDBCUtils jdbcInstance=null;
//		Connection conn=null;
//		PreparedStatement pstmt = null;
//		String sql = "SELECT host_name FROM system.clusters WHERE cluster = 'cluster_ck'";
//
//		List<String> ckServerList = new ArrayList<>();
//		try{
//			jdbcInstance = JDBCUtils.getInstance(config);
//			conn = jdbcInstance.getClickhouseConnection();
//			pstmt = conn.prepareStatement(sql);
//			ResultSet rest = pstmt.executeQuery();
//
//			while (rest.next()) {
//				String hostAddress = rest.getString("host_name");
//				ckServerList.add(hostAddress);
//			}
//		} catch (SQLException e) {
//			logger.error("ck quere sql exception,SQL -- {}",sql);
//			logger.error("ck 执行sql异常！",e);
//		} catch(Exception e) {
//			logger.error("获取数据库连接异常",e);
//			throw e;
//		} finally {
//			try {
//				pstmt.close();
//			} catch (SQLException e) {
//				logger.error("ck executeCkSql close sql exception",e);
//			} finally {
//				jdbcInstance.closeConnect(conn);
//			}
//		}
//
//		return  ckServerList;
//	}

	public void executeSqlInBatches(String sql, List<List<SqlParameter>> params) throws Exception {
		JDBCUtils jdbcInstance = null;
		Connection conn = null;
		PreparedStatement statement = null;
		try{
			jdbcInstance = JDBCUtils.getInstance(config);
			conn = jdbcInstance.getClickhouseConnection();
			statement = conn.prepareStatement(sql);

			Object[][] transformParams = new Object[params.size()][1];//assemble batch insert params
			for (int i = 0; i < params.size(); i++) {
				transformParams[i] = params.get(i).toArray();
				int parameterIndex = 1;
				for (int index = 0; index < transformParams[i].length; index++) {
					SqlParameter sqlParameter = (SqlParameter) transformParams[i][index];
					String fieldType = String.valueOf(sqlParameter.getFieldType());
					Object fieldValue = sqlParameter.getFieldValue();
					String transformValue = String.valueOf(fieldValue);
					List<List<String>> extendFieldData = sqlParameter.getExtendFieldData();

					if (StringUtils.contains(fieldType, ClickHouseDataType.Array.name())) {
						ClickHouseArray clickHouseArray;
						if (StringUtils.equals(transformValue, "null") || StringUtils.isBlank(transformValue)) {
							clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, new Object[0]);
						} else {
							clickHouseArray = convert2ClickHouseArray(fieldType, transformValue);
						}
						statement.setArray(parameterIndex++, clickHouseArray);
					} else if (StringUtils.contains(fieldType, ClickHouseDataType.Nested.name())) {
						for(int j=0; j<2; j++){
							ClickHouseArray clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, extendFieldData.get(j).toArray());
							statement.setArray(parameterIndex++, clickHouseArray);
						}
					} else {
						statement.setString(parameterIndex++, String.valueOf(transformValue));
					}
				}
				statement.addBatch();
			}
			statement.executeBatch();
		} catch (SQLException e) {
			logger.error("executeSql exception,SQL -- "+sql);
			logger.error("DB::Exception: Execute SQL In Batches Failed", e);
			throw e;
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(statement !=null){
					statement.close();
				}else {
					logger.info("statement is null.......");
				}
			} catch (SQLException e) {
				logger.error("statement.close() ",e);
			} finally {
				if (conn != null) {
					jdbcInstance.closeConnect(conn);
				} else {
					logger.info("conn is null.......");
				}
			}
		}
	}


//		public void executeSqlInBatches(String sql, List<List<SqlParameter>> params) {
//		JDBCUtils jdbcInstance = null;
//		Connection conn = null;
//		PreparedStatement statement = null;
//		try{
//			jdbcInstance = JDBCUtils.getInstance(config);
//			conn = jdbcInstance.getClickhouseConnection();
//			statement = conn.prepareStatement(sql);
//
//			Object[][] transformParams = new Object[params.size()][1];//assemble batch insert params
//			for (int i = 0; i < params.size(); i++) {
//				transformParams[i] = params.get(i).toArray();
//				int parameterIndex = 1;
//				for (int index = 0; index < transformParams[i].length; index++) {
//					SqlParameter sqlParameter = (SqlParameter) transformParams[i][index];
//					Object fieldValue = sqlParameter.getFieldValue();
//					String transformValue = String.valueOf(fieldValue);
//					statement.setString(parameterIndex++, String.valueOf(transformValue));
//				}
//				statement.addBatch();
//			}
//			statement.executeBatch();
//		} catch (SQLException e) {
//			logger.error("executeSql exception,SQL -- "+sql,null);
//			logger.error("DB::Exception: Execute SQL In Batches Failed", e);
//		} catch(Exception e) {
//			throw e;
//		} finally {
//			try {
//				statement.close();
//			} catch (SQLException e) {
//				e.printStackTrace();
//			} finally {
//				jdbcInstance.closeConnect(conn);
//			}
//		}
//	}

	/**
	 * convert some filed value 2 ClickHouseArray type
	 *
	 * @param fieldType Filed Type
	 * @param fieldValue Field Value
	 * @return ClickHouseArray Instance
	 */
	private ClickHouseArray convert2ClickHouseArray(String fieldType, String fieldValue) {
		ClickHouseArray clickHouseArray = null;
		Object[] array = null;

		if (StringUtils.isBlank(fieldValue)) {
			array = new Object[0];
			clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, array);
		} else {
			fieldType = fieldType.substring(fieldType.indexOf('(')+1, fieldType.indexOf(')'));
			String[] strArray = fieldValue.replace("[","").replace("]","").split(Constants.COMMA_SEPARATOR);
			switch (fieldType) {
				case Constants.CK_FIELD_TYPE_INT_8: case Constants.CK_FIELD_TYPE_INT_16: case Constants.CK_FIELD_TYPE_INT_32:
					int[] intArray = (int[]) ConvertUtils.convert(strArray, int.class);
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, intArray);
					break;
				case Constants.CK_FIELD_TYPE_INT_64:
					int[] longArray = (int[])ConvertUtils.convert(strArray, long.class);
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, longArray);
					break;
				case Constants.CK_FIELD_TYPE_FLOAT_32: case Constants.CK_FIELD_TYPE_FLOAT_64:
					float[] floatArray = (float[])ConvertUtils.convert(strArray, float.class);
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, floatArray);
					break;
				case Constants.CK_FIELD_TYPE_STRING:
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, strArray);
					break;
				case Constants.CK_FIELD_TYPE_DATETIME:
					Timestamp[] timestampArray = (Timestamp[]) ConvertUtils.convert(strArray, Timestamp.class);
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.DateTime, timestampArray);
					break;
				case Constants.CK_FIELD_TYPE_DATE:
					Date[] dateArray = (Date[]) ConvertUtils.convert(strArray, Date.class);
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Date, dateArray);
					break;
				default:
					clickHouseArray = new ClickHouseArray(ClickHouseDataType.Array, strArray);
					break;
			}
		}

		return clickHouseArray;
	}


//	public void deleteCk(String sql, List<List<String>>params,String hostName) {
//		JDBCUtils jdbcInstance=null;
//		Connection conn=null;
//		PreparedStatement pstmt = null;
//		try{
//			jdbcInstance = JDBCUtils.getInstance(config);
//			conn = jdbcInstance.getClickhouseConnection(hostName);
//			pstmt = conn.prepareStatement(sql);
//
//			Object[][] params1 = new Object[params.size()][1];//assemble batch insert params
//			for (int i = 0; i < params.size(); i++) {
//				params1[i] = params.get(i).toArray();
//				for (int index = 0; index < params1[i].length; index++) {
//					pstmt.setString(index+1, params1[i][index]+"");
//				}
//				logger.info("ck sql params:"+params.get(i));
//				pstmt.addBatch();
//			}
//			pstmt.executeBatch();
//		} catch (SQLException e) {
//			logger.error("ck executeSql exception,SQL -- {}",sql);
//			logger.error("ck 执行sql异常！",e);
//		} catch(Exception e) {
//			logger.error("获取数据库连接异常",e);
//			throw e;
//		} finally {
//			try {
//				pstmt.close();
//			} catch (SQLException e) {
//				logger.error("ck executeCkSql close sql exception",e);
//			} finally {
//				jdbcInstance.closeConnect(conn);
//			}
//		}
//	}


	public boolean queryCk(String sql) {
		JDBCUtils jdbcInstance = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rest = null;
		int ncount = 0;
		try {
			jdbcInstance = JDBCUtils.getInstance(config);
			conn = jdbcInstance.getClickhouseConnection();
			pstmt = conn.prepareStatement(sql);
			rest = pstmt.executeQuery();

			if (rest.next()) {
				ncount = rest.getInt("TOTAL_NUM");
			}
		} catch (SQLException e) {
			logger.error("ck quere sql exception,SQL -- " + sql);
			logger.error("ck 执行sql异常！", e);
		} catch (Exception e) {
			logger.error("获取数据库连接异常", e);
			throw e;
		} finally {
			if (rest != null) {
				try {
					rest.close();
				} catch (SQLException e) {
					logger.error("rest.close() ",e);
				}
			}
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				logger.error("ck executeCkSql close sql exception", e);
			} finally {
				if(jdbcInstance!=null){
					jdbcInstance.closeConnect(conn);
				}
			}
		}

		return ncount > 0;
	}

}